package com.bw.gmallbi.mapper;



import com.bw.gmallbi.bean.ProductStats;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

// Desc: 商品统计的Mapper接口
public interface ProductStatsMapper {
    // 获取某天商品的成交总金额
    @Select("select sum(order_amount) order_amount from product_stats_2105b where toYYYYMMDD(stt)=#{date}")
    BigDecimal selectGMV(Integer date);

    @Select("select tm_name,sum(order_amount) order_amount from  product_stats_2105b where toYYYYMMDD(stt)=#{date} group by tm_name  order by order_amount desc limit #{limit}")
    List<ProductStats> selectGMVByTm(@Param("date") Integer date, @Param("limit") Integer limit);

    @Select("select category3_name,sum(order_amount) order_amount from  product_stats_2105b where toYYYYMMDD(stt)=#{date} group by category3_id,category3_name  order by order_amount desc limit #{limit}")
    List<ProductStats> selectGMVByCt(@Param("date") Integer date, @Param("limit") Integer limit);

    // 每个商品的销售情况
    @Select("select spu_id,spu_name,sum(order_amount) order_amount from product_stats_2105b " +
            "where toYYYYMMDD(stt)=#{date} group by spu_id,spu_name having order_amount > 0 " +
            "order by order_amount desc limit #{limit}")
    List<ProductStats> selectProductStatsBySPU(@Param("date") Integer date, @Param("limit") Integer limit);
}
